package pfc.etsii.ull.es.server.SQLUtils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;

import pfc.etsii.ull.es.shared.generated.Informe;
import pfc.etsii.ull.es.shared.generated.SchemaDatos.Consulta;


public class SQL {
	
	private static Connection conexion = null;
	
	public SQL() {
		
	}
	
	
	public static String crearConexionSQL() {	
		Properties props = new Properties();
		FileInputStream in;
		try {
			in = new FileInputStream("db.properties");
			props.load(in);
			in.close();
			String driver = props.getProperty("jdbc.driver");
		    if (driver != null) {
		    	Class.forName(driver) ;
		    }
			String url = props.getProperty("jdbc.url");
			String username = props.getProperty("jdbc.username");
			String password = props.getProperty("jdbc.password");
			
			conexion = (Connection) DriverManager.getConnection(url, username, password);
			
			return "OK";
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return "KO";
	}
	
	public static List <String> ejectuarConsultaSimple (String consulta) {
		crearConexionSQL();
		crearConexionSQL();
		List <String> resultado = new ArrayList<String>();
		
		PreparedStatement consultaStatement;
		try {
			consultaStatement = conexion.prepareStatement(consulta);
			ResultSet result1 = consultaStatement.executeQuery();
			while (result1.next()) {
				resultado.add(result1.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			cerrarConexionMySQL();
			return null;	
		}
		
		
		return resultado;
		
	}

	
	public static Map<String, List<String>> ejecutarConsulta(String consulta) {
		crearConexionSQL();
		Map<String, List<String>> content = new HashMap<String, List<String>>();
		
		PreparedStatement consulta1;
		try {
			consulta1 = conexion.prepareStatement(consulta);
			ResultSet result1 = consulta1.executeQuery();
			ResultSetMetaData rsMetaData = result1.getMetaData();

		    int numberOfColumns = rsMetaData.getColumnCount();
		    
		    
		    for (int i = 1; i <= numberOfColumns; i++) {
		    	List<String> serie = new ArrayList<String>();
		    	while (result1.next()) {
		    		serie.add(result1.getString(i));
		    	}
		    	content.put(rsMetaData.getColumnName(i), serie);
		    	result1.beforeFirst();
		    }
			
		} catch (SQLException e) {
			e.printStackTrace();
			cerrarConexionMySQL();
			return null;	
		}
		
		cerrarConexionMySQL();
		return content;
	}
	
	
	public static String sustituirParametros(Map<String, List<String>> parametros, String consulta) {
		
		for (Entry<String, List<String>> parametroActual : parametros.entrySet()) {
			
			consulta = consulta.replace(parametroActual.getKey(), parametroActual.getValue().get(0));
		}
		
		return consulta;
		
	}
	
	public static String sustituirConsulta(String id, Informe datosXML) {
		
		if (id != null) {
		
			id = id.trim();
			
			List<Consulta> consultas = datosXML.getDatos().getConsulta();
			
			for (Consulta actual : consultas) {
				if (actual.getID().equals(id)) {
					return actual.getSQL();
				}
			}
			
			return null;
			
		}
		return null;
	}
	
	
	
	public static void cerrarConexionMySQL() {
		
		try {
			conexion.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

}
